﻿CREATE PROCEDURE [ProductFeedback].[GetProductFeedback]
	@TenantId uniqueidentifier,
	@CatalogId [ProductFeedback].[TCatalogId],
	@ProductId [ProductFeedback].[TProductId]
AS

-- 1st
SELECT
	PF.FeedbackId,
	PF.CultureId,
	PF.DateAdded,
	PF.CustomerId,
	PF.CustomerName,
	PF.CustomerLocation,
	PF.EditorComments
FROM
	ProductFeedback PF
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 2nd
SELECT
	PF.FeedbackId,
	PFBA.BadgeId
FROM
	ProductFeedback PF 
	JOIN ProductFeedbackBadgeAssociation PFBA ON PF.TenantId = PFBA.TenantId AND PF.FeedbackId = PFBA.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 3rd
SELECT
	PF.FeedbackId,
	PR.Value
FROM
	ProductFeedback PF 
	JOIN ProductRating PR ON PF.TenantId = PR.TenantId AND PF.FeedbackId = PR.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 4th
SELECT
	PF.FeedbackId,
	PRAV.AttributeId,
	PRAV.Value
FROM
	ProductFeedback PF 
	JOIN ProductRatingAttributeValue PRAV ON PF.TenantId = PRAV.TenantId AND PF.FeedbackId = PRAV.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 5th
SELECT
	PF.FeedbackId,
	PR.Value
FROM
	ProductFeedback PF 
	JOIN ProductRecommendation PR ON PF.TenantId = PR.TenantId AND PF.FeedbackId = PR.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 6th
SELECT
	PF.FeedbackId,
	PRAV.AttributeId,
	PRAV.Value
FROM
	ProductFeedback PF 
	JOIN ProductRecommendationAttributeValue PRAV ON PF.TenantId = PRAV.TenantId AND PF.FeedbackId = PRAV.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 7th
SELECT
	PF.FeedbackId,
	PR.Title,
	PR.Body
FROM
	ProductFeedback PF 
	JOIN ProductReview PR ON PF.TenantId = PR.TenantId AND PF.FeedbackId = PR.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 8th
SELECT
	PF.FeedbackId,
	PRAV.AttributeId,
	PRAV.Value
FROM
	ProductFeedback PF 
	JOIN ProductReviewAttributeValue PRAV ON PF.TenantId = PRAV.TenantId AND PF.FeedbackId = PRAV.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 9th
SELECT
	PF.FeedbackId,
	PRI.ImageId,
	PRI.Caption,
	PRI.ThumbnailPath,
	PRI.ThumbnailWidth,
	PRI.ThumbnailHeight,
	PRI.ImagePath,
	PRI.ImageWidth,
	PRI.ImageHeight
FROM
	ProductFeedback PF 
	JOIN ProductReviewImage PRI ON PF.TenantId = PRI.TenantId AND PF.FeedbackId = PRI.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 10th
SELECT
	PF.FeedbackId,
	PRV.StillPath,
	PRV.StillWidth,
	PRV.StillHeight,
	PRV.VideoPath,
	PRV.VideoWidth,
	PRV.VideoHeight,
	PRV.Length,
	PRV.Transcript
FROM
	ProductFeedback PF 
	JOIN ProductReviewVideo PRV ON PF.TenantId = PRV.TenantId AND PF.FeedbackId = PRV.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId

-- 11th
SELECT
	PF.FeedbackId,
	COUNT(*) AS TotalThirdPartyReviews,
	SUM(CAST(PRTPR.Value as int)) AS PositiveThirdPartyReviews
FROM
	ProductFeedback PF 
	JOIN ProductReviewThirdPartyRating PRTPR ON PF.TenantId = PRTPR.TenantId AND PF.FeedbackId = PRTPR.FeedbackId
WHERE
	PF.TenantId = @TenantId
	AND PF.CatalogId = @CatalogId
	AND PF.ProductId = @ProductId
GROUP BY
	PF.FeedbackId
